/*
安装
go get -u github.com/go-sql-driver/mysql
并手动在import中添加：
_ "github.com/go-sql-driver/mysql"
*/

package db

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"os/exec"
	"server/cfg"
	"strings"
	"sync"
)

var (
	_db  *sql.DB
	once sync.Once
)

func CreateDb(dbName string) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/mysql", cfg.Config.Db.Username, cfg.Config.Db.Password, cfg.Config.Db.Host, cfg.Config.Db.Port)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Println("打开数据库失败：", err)
	}
	defer func(db *sql.DB) {
		_ = db.Close()
	}(db)
	var result string
	err = db.QueryRow("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=?", dbName).Scan(&result)
	isDbExist := err == nil && result == dbName
	if isDbExist {
		fmt.Println("数据库", dbName, "已存在")
	} else {
		_, err = db.Exec("CREATE" + " DATABASE " + dbName)
		if err != nil {
			log.Println("数据库", dbName, "创建失败", err)
		} else {
			fmt.Println("数据库", dbName, "创建成功")
		}
	}
}

func ExportDb(savePath string) (err error) {
	host := cfg.Config.Db.Host
	username := cfg.Config.Db.Username
	password := cfg.Config.Db.Password
	dbName := cfg.Config.Db.DbName
	cmd := exec.Command("mysqldump", "-h", host, "-u", username, "-p"+password, dbName, "--result-file="+savePath)
	err = cmd.Run()
	return
}

func DeleteDb() (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/mysql", cfg.Config.Db.Username, cfg.Config.Db.Password, cfg.Config.Db.Host, cfg.Config.Db.Port)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Println("打开系统数据库失败：", err)
	}
	defer func(db *sql.DB) {
		err := db.Close()
		if err != nil {
		}
	}(db)

	// 测试连接是否成功
	err = db.Ping()
	if err != nil {
		log.Println("连接数据库失败：", err)
	}

	// 执行 DROP DATABASE 语句
	query := fmt.Sprintf("DROP DATABASE IF EXISTS `%s`", cfg.Config.Db.DbName)
	_, err = db.Exec(query)
	if err != nil {
		log.Println("删除数据库失败：", err)
	}
	return
}

func Db() *sql.DB {
	once.Do(func() {
		var err error
		dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?time_zone='%%2B08:00'", cfg.Config.Db.Username, cfg.Config.Db.Password, cfg.Config.Db.Host, cfg.Config.Db.Port, cfg.Config.Db.DbName)
		_db, err = sql.Open("mysql", dsn)
		if err != nil {
			log.Println("打开数据库失败：", err, dsn)
		}
		err = _db.Ping()
		if err != nil {
			log.Println("连接数据库失败：", err, dsn)
		}
		_db.SetMaxOpenConns(cfg.Config.Db.MaxOpenConnects)
		_db.SetMaxIdleConns(cfg.Config.Db.MaxIdleConnects)
	})
	return _db
}

// 求行数。例：count := Count("student", "gender=? and age<?", 'boy', 30)
func Count(table string, whereQuery string, values ...any) (count int64) {
	query := "select" + " coalesce(count(*), 0) from " + table
	if whereQuery != "" {
		query += " where " + whereQuery
	}
	err := Db().QueryRow(query, values...).Scan(&count)
	if err != nil {
		log.Println("查询数量失败：", query, values, err)
	}
	return
}

// 求行数。例：count := Count("student", "zhang", "gender=? and age<?", 'boy', 30)
func CountDistinct(table string, distinctColumn string, whereQuery string, values ...any) (count int64) {
	query := "select" + " coalesce(count(distinct " + distinctColumn + "), 0) from " + table
	if whereQuery != "" {
		query += " where " + whereQuery
	}
	err := Db().QueryRow(query, values...).Scan(&count)
	if err != nil {
		log.Println("查询去重数量失败：", query, values, err)
	}
	return
}

// 求总和。例：total := TotalInt64("orders", "amount", "create_time>=? and create_time<=?", '2025-01-01', '2025-12-31')
func TotalInt64(table, column string, whereQuery string, values ...any) (sum int64) {
	query := "select coalesce(sum(" + column + "), 0) from " + table
	if whereQuery != "" {
		query += " where " + whereQuery
	}
	err := Db().QueryRow(query, values...).Scan(&sum)
	if err != nil {
		log.Println("查询总和失败：", query, values, err)
	}
	return
}

// 求总和。例：total := TotalFloat64("orders", "amount", "create_time>=? and create_time<=?", '2025-01-01', '2025-12-31')
func TotalFloat64(table, column string, whereQuery string, values ...any) (sum float64) {
	query := "select coalesce(sum(" + column + "), 0) from " + table
	if whereQuery != "" {
		query += " where " + whereQuery
	}
	err := Db().QueryRow(query, values...).Scan(&sum)
	if err != nil {
		log.Println("查询总和失败：", query, values, err)
	}
	return
}

// 查询是否存在。例：isExist := IsExist("student", "gender=? and age<?", 'boy', 30)
func IsExist(table string, whereQuery string, values ...any) bool {
	if strings.ContainsAny(whereQuery, "limit") {
		whereQuery += " limit 1"
	}
	query := "select" + " coalesce(count(*), 0) from " + table
	if whereQuery != "" {
		query += " where " + whereQuery
	}
	count := 0
	err := Db().QueryRow(query, values...).Scan(&count)
	if err != nil {
		log.Println("查询是否存在失败：", query, values, err)
	}
	return count > 0
}

// 查询表否存在
func IsTableExist(table string) bool {
	var count int
	err := Db().QueryRow("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?", table).Scan(&count)
	if err != nil {
		return false
	}
	return count > 0
}

func Select(sql string, values ...any) (rows []map[string]any) {
	rows = make([]map[string]any, 0)
	results, err := Db().Query(sql, values...)
	if err != nil {
		log.Println("查询数据失败：", sql, err)
		return
	}
	defer func() {
		_ = results.Close()
	}()

	// 获取列名和列数
	cols, err := results.Columns()
	if err != nil {
		log.Println("获取列名失败：", sql, err)
		return
	}
	colsCount := len(cols)

	// 读取每一行
	for results.Next() {
		rowData := make([]any, colsCount)
		rowPointers := make([]any, colsCount)
		for i := range rowData {
			rowPointers[i] = &rowData[i]
		}

		// 扫描每一行的数据
		err = results.Scan(rowPointers...)
		if err != nil {
			log.Println("查询数据失败：", sql, err)
			return
		}

		// 处理每一行的值
		rowMap := make(map[string]any)
		for i, colName := range cols {
			value := rowData[i]
			if b, ok := value.([]byte); ok {
				rowMap[colName] = string(b)
			} else {
				rowMap[colName] = value
			}
		}

		// 将当前行的数据添加到结果集中
		rows = append(rows, rowMap)
	}
	// 检查是否有错误发生
	if err := results.Err(); err != nil {
		log.Println("查询数据失败：", sql, err)
	}
	return
}

func Exec(sql string, values ...any) sql.Result {
	result, err := Db().Exec(sql, values...)
	if err != nil {
		log.Println("执行数据库失败:", sql, err)
	}
	return result
}

func GetNextAutoIncrementId(tableName string) (int64, error) {
	var nextID int64
	query := `SELECT AUTO_INCREMENT 
              FROM information_schema.TABLES 
              WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`

	err := Db().QueryRow(query, cfg.Config.Db.DbName, tableName).Scan(&nextID)
	if err != nil {
		return 0, err
	}
	return nextID, nil
}
